


/* The scope of this procedure is to return status of particular sales order */


ALTER       PROCEDURE sp_sales_order_status_for_search 

	@salesorder_id 			varchar (30),
	@status				varchar (100) output
AS

begin
	declare @cool_qty 		int
	declare @cslit_shipped_qty 	int
	declare @cslit_issued_qty 	int

	declare @temp_status 		varchar(100)
	declare salesOrderStatusCursor 	cursor for
	select distinct hold_by_group from sales_hold_table where salesorder_id = @salesorder_id and is_approved = 1

	set @status = ''

	open salesOrderStatusCursor
	fetch next from salesOrderStatusCursor into @temp_status
	while @@fetch_status = 0
	begin
		set @status = @status + ' ' +@temp_status
		fetch next from salesOrderStatusCursor into @temp_status
	end
	close salesOrderStatusCursor
	deallocate salesOrderStatusCursor
	
	if @status = ''
	begin
		select @status = so_status from sales_order where salesorder_id = @salesorder_id

		if @status = 'APPROVED'
		begin
			select @cool_qty = isnull(sum(cool_qty),0) from customer_open_order_lineitem where salesorder_id = @salesorder_id

			select @cslit_shipped_qty = isnull(sum(cslit_qty_shipped),0) 
						from customer_open_order_lineitem , customer_shipping_line_item_table 
						where customer_open_order_lineitem.salesorder_id = @salesorder_id and 
						cslit_status='SHIPPED'and 
						customer_open_order_lineitem.lineitem_id=customer_shipping_line_item_table.lineitem_id

			select @cslit_issued_qty = isnull(sum(cslit_qty_shipped),0) 
						from customer_open_order_lineitem , customer_shipping_line_item_table 
						where customer_open_order_lineitem.salesorder_id = @salesorder_id and 
						cslit_status='ISSUED'and 
						customer_open_order_lineitem.lineitem_id=customer_shipping_line_item_table.lineitem_id

			if @cool_qty = @cslit_shipped_qty
			begin
				set @status = 'SHIPPED'
			end
			else if @cslit_shipped_qty > 0
			begin
				set @status = 'PARTIALLY SHIPPED'
			end
			else if @cslit_shipped_qty = 0
			begin
				if @cslit_issued_qty > 0
				begin
					set @status = 'PS ISSUED'
				end
			end
		end
	end
	else
	begin
		set @status = 'HOLD BY - ' + @status
	end

	-- print @status
	return
end




